#!/usr/bin/python
#-*- coding: utf-8 -*-
# 制作映射，字典，缓存文件
# 作者：王成
# 日期：2017-04-16
import MySQLdb
import json
import yaml,logging
from logging.handlers import TimedRotatingFileHandler,RotatingFileHandler
import time,timeit,datetime,sys,os
import traceback
import StringIO
import pickle
import web
from daemon import Daemon

render = web.template.render('templates/')

class Index:
    def execute_sql(self,sql,action='select'):
        '''数据库连接 & 执行SQL'''
        try:
            db = MySQLdb.connect(host=web.config['mysql_web']['host'],port=web.config['mysql_web']['port'], user=web.config['mysql_web']['user'], passwd=web.config['mysql_web']['password'], db=web.config['mysql_web']['database'],charset="utf8") 
            mysql_web = db.cursor(MySQLdb.cursors.DictCursor)
            r = mysql_web.execute(sql)
            if action=='select':
                r = mysql_web.fetchall()
            elif action=='update':
                pass
            elif action=='insert':
                r = db.insert_id()
            mysql_web.close()
            db.close()
            return r
        except Exception, e:
            logging.exception('连接数据库时错误: %s', str(e))
            r = None
            if action=='select':
                r = []
            elif action=='update':
                pass
            elif action=='insert':
                r = 0
            return r
            
    def make_location_dict_cache(self):
        '''制作 第三方卡口ID=>以萨卡口ID 的映射字典'''
        try:
            all_location = self.execute_sql("SELECT location_id,loc_id FROM `mon_location` WHERE delete_flag = 0;",action='select')
            d = {} 
            for location in all_location:
                # print location['loc_id'],location['location_id']
                d[str(location['loc_id'])] = str(location['location_id'])
            d_str = pickle.dumps(d)
            f=file(os.path.dirname(os.path.abspath(__file__))+"/data/location_dict.dat","wb")
            f.write(d_str)
            f.close()
            # with open('/yisa_oe/message/data/location_dict.dat') as f:  
                # d_str = f.read()
                # print pickle.loads(d_str)
            return True,len(d)
        except Exception, e:
            logging.exception('制作卡口缓存时错误: %s', str(e))
            return False,0
                
    def update_location_id(self):
        '''根据数据库自增ID及区域编码更新以萨卡口ID'''
        try:
            c = self.execute_sql("UPDATE mon_location as ml SET ml.location_id = ml.region_code*1000000+ml.id WHERE ml.location_id = 0 AND ml.region_code !=0;",action='update')        
            return True,c
        except Exception, e:
            logging.exception('更新卡口ID时错误: %s', str(e))
            return False,0

    def make_device_dict_cache(self):
        '''制作 第三方设备ID=>以萨设备ID 的映射字典'''
        try:       
            all_device = self.execute_sql("SELECT device_id,dev_id FROM `mon_device` WHERE delete_flag = 0;",action='select')
            d = {} 
            for device in all_device:
                # print device['dev_id'],device['device_id']
                d[str(device['dev_id'])] = str(device['device_id'])
            d_str = pickle.dumps(d)
            f=file(os.path.dirname(os.path.abspath(__file__))+"/data/device_dict.dat","wb")
            f.write(d_str)
            f.close()
            # with open('/yisa_oe/message/data/device_dict.dat') as f:  
                # d_str = f.read()
                # print pickle.loads(d_str)                  
            return True,len(d)
        except Exception, e:
            logging.exception('制作设备缓存时错误: %s', str(e))
            return False,0
            
    def update_device_id(self):
        '''根据数据库自增ID及区域编码更新以萨设备ID'''
        try:    
            self.execute_sql("UPDATE mon_device as md,mon_location as ml SET md.location_id = ml.location_id WHERE md.location_id=0 AND md.loc_id = ml.loc_id;",action='update')
            c = self.execute_sql("UPDATE mon_device as md SET md.device_id = md.location_id*1000000+md.id WHERE md.device_id = 0 AND md.location_id !=0;",action='update')
            return True,c
        except Exception, e:
            logging.exception('更新设备ID时错误: %s', str(e))
            return False,0          
          
    def make_device_to_location_dict_cache(self):
        '''制作 以萨设备ID=>以萨卡口ID 的映射字典'''
        try:        
            all_device = self.execute_sql("SELECT device_id,location_id FROM `mon_device` WHERE delete_flag = 0;",action='select')
            d = {} 
            for device in all_device:
                d[str(device['device_id'])] = str(device['location_id'])
            d_str = pickle.dumps(d)
            f=file(os.path.dirname(os.path.abspath(__file__))+"/data/device_to_location_dict.dat","wb")
            f.write(d_str)
            f.close()           
            return True,len(d)
        except Exception, e:
            logging.exception('制作设备到卡口缓存时错误: %s', str(e))
            return False,0          
    
    def make_yearid_to_info_dict_cache(self):
        '''制作 以萨year_id 到 品牌型号年款 的映射字典'''
        try:        
            all_info = self.execute_sql("SELECT cy.brandID,cy.modelID,cy.yearID,cm.levelID FROM `car_model` as cm,car_year as cy WHERE cm.modelID = cy.modelID;",action='select')
            d = {} 
            for info in all_info:
                d[info['yearID']] = {'brandID':info['brandID'],'modelID':info['modelID'],'levelID':info['levelID']}
            d_str = pickle.dumps(d)
            f=file(os.path.dirname(os.path.abspath(__file__))+"/data/yearid_to_info_dict.dat","wb")
            f.write(d_str)
            f.close()           
            return True,len(d)
        except Exception, e:
            logging.exception('制作year_id到品牌型号年款缓存时错误: %s', str(e))
            return False,0  
    
    def format_output(self,r,c,msg,format='html'):
        '''格式化输出信息'''
        if r:
            if format == 'html':
                return render.index(state=1, msg=msg)
            else:
                return json.dumps({'state':r,'count':c})
        else:
            if format == 'html':
                return render.index(state=1, msg="失败")
            else:
                return json.dumps({'state':r,'count':c})
                
    def GET(self):
        web.header("Content-Type","text/html; charset=utf-8")
        #print web.input()
        user_data = web.input(action=None,format='html')
        if user_data.action=='make_location_dict_cache':
            r,c = self.make_location_dict_cache()
            return self.format_output(r,c,msg="成功,字典长度%d,请重启yisa_get_msg脚本" % c,format=user_data.format)
        elif user_data.action=='update_location_id':
            r,c = self.update_location_id()
            return self.format_output(r,c,msg="成功,更新%d行" % c,format=user_data.format)
        elif user_data.action=='make_device_dict_cache':
            r,c = self.make_device_dict_cache()
            return self.format_output(r,c,msg="成功,字典长度%d,请重启yisa_get_msg脚本" % c,format=user_data.format)
        elif user_data.action=='update_device_id':
            r,c = self.update_device_id()
            return self.format_output(r,c,msg="成功,更新%d行" % c,format=user_data.format)
        elif user_data.action=='make_device_to_location_dict_cache':
            r,c = self.make_device_to_location_dict_cache()
            return self.format_output(r,c,msg="成功,字典长度%d" % c,format=user_data.format)
        elif user_data.action=='make_yearid_to_info_dict_cache':
            r,c = self.make_yearid_to_info_dict_cache()
            return self.format_output(r,c,msg="成功,字典长度%d" % c,format=user_data.format)            
        else:        
            return render.index(state=0, msg="")
        
class MyDaemon(Daemon):
    def run(self):
        self.urls = (  
                '/', 'Index'
                )  
        self.app = web.application(self.urls, globals())
        try:
            config_file = open(os.path.dirname(os.path.abspath(__file__)) + '/config.yaml')
            web.config = yaml.safe_load(config_file)
            config_file.close()

            name = 'yisa_web_tools'
            logging.basicConfig(level=logging.INFO)
            handler = RotatingFileHandler('/var/log/%s.log' % name, maxBytes=134217728, backupCount=7)
            formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
            handler.setFormatter(formatter)
            logging.getLogger('').addHandler(handler)
            #-------------------同步输出到控制台-------------------
            # console = logging.StreamHandler()
            # console.setLevel(logging.INFO)
            # formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
            # console.setFormatter(formatter)
            # logging.getLogger('').addHandler(console)
            #-------------------------------------------------------
            logging.warning('启动 [%s]', name)        
            sys.argv[1] = web.config['web_tools']['listen']
            logging.warning('监听 [%s]', sys.argv[1])
            self.app.run()
        except KeyboardInterrupt:
            sys.exit()
        except Exception, e:
            logging.exception('启动 [%s] 错误: %s', name,str(e))
    
if __name__ == "__main__": 
    daemon = MyDaemon('/var/run/yisa_web_tools.pid')
    #daemon.run()
    #sys.exit()
    if len(sys.argv) == 2:
        if 'start' == sys.argv[1]:
            daemon.start()
        elif 'stop' == sys.argv[1]:
            daemon.stop()
        elif 'restart' == sys.argv[1]:
            daemon.restart()
        else:
            daemon.run()
            print "Unknown command"
            sys.exit(2)
        sys.exit(0)
    else:
        print "usage: %s start|stop|restart" % sys.argv[0]
        sys.exit(2)
        
